package com.jucagal.kiosco.sql;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JTable;

import com.jucagal.kiosco.main.Main;
import com.jucagal.kiosco.sql.Code;
import com.jucagal.kiosco.table.FillTable;

public class SqlConnection {

	// caracol
//		private static final String url = "jdbc:sqlserver://172.17.86.99";
//		private static final String className = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//		private static final String user = "java";
//		private static final String password = "999";

		// Portatil
		 private static final String url =
		 "jdbc:sqlserver://localhost\\SQLEXPRESS";
		 private static final String className =
		 "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		 private static final String user = "javauser";
		 private static final String password = "999";

		private static String selectStatementCodigo, selectStatementDocumento, selectStatementDate;
		
		static {
			if (Main.test) {
				selectStatementCodigo = "select cedula_usuario, nombre_usuario, codigo, dispositivo, marca, image, ticketEnable, serial from codigos where codigo = ? order by id desc";
				selectStatementDocumento = "select inserted_date, cedula_usuario, nombre_usuario, codigo, dispositivo, marca, ticketEnable, serial from codigos where cedula_usuario = ? order by id desc";
				selectStatementDate = "select inserted_date, cedula_usuario, nombre_usuario, codigo, dispositivo, marca, ticketEnable, serial from codigos where inserted_date between ? and ? order by id desc";
			} else {
				selectStatementCodigo = "select cedula_usuario, nombre_usuario, codigo, dispositivo, marca, image, ticketEnable, serial  from [A1ABIOIDTAC].[dbo].codigos where codigo = ? order by id desc";
				selectStatementDocumento = "select inserted_date, cedula_usuario, nombre_usuario, codigo, dispositivo, marca, ticketEnable, serial from [A1ABIOIDTAC].[dbo].codigos where cedula_usuario = ? order by id desc";
				selectStatementDate = "select inserted_date, cedula_usuario, nombre_usuario, codigo, dispositivo, marca, ticketEnable, serial from [A1ABIOIDTAC].[dbo].codigos where inserted_date between ? and ? order by id desc";
			}
		}
		
		public static Code consultarCodigo(String codigo) {
			try {
				List<Code> codigos = new ArrayList<Code>();
				Class.forName(className);
				Connection con = DriverManager.getConnection(url, user, password);
				PreparedStatement st = con
						.prepareStatement(selectStatementCodigo);
				st.setString(1, codigo);
				ResultSet resultado = st.executeQuery();

				while (resultado.next()) {
					Code consulta = new Code();
					consulta.cedulaUsuario = resultado.getString(1);
					consulta.nombreUsuario = resultado.getString(2);
					consulta.codigo = resultado.getString(3);
					consulta.dispositivo = resultado.getString(4);
					consulta.marca = resultado.getString(5);
					consulta.imagen = new ByteArrayInputStream(
							resultado.getBytes(6));
					consulta.ticketEnable = resultado.getString(7);
					consulta.serial = resultado.getString(8);
					codigos.add(consulta);
				}
				st.close();
				con.close();
				if (codigos.size() > 0) {
					return codigos.get(0);
				}
				return null;
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;

		}
		
		 public static FillTable myModel(String codigo) throws Exception{
			 try {
			 Class.forName (className);
			 } catch (ClassNotFoundException e) {
			 e.printStackTrace();
			 }
			 
			 Connection con = DriverManager.getConnection(url, user, password);
			 PreparedStatement st = con.prepareStatement(selectStatementDocumento);
				st.setString(1, codigo);
				ResultSet rs = st.executeQuery();
			 FillTable model = new FillTable(rs);
			 return model;
			 }
		 
		 public static FillTable myModelDate(String codigoFrom, String codigoTo) throws Exception{
			 try {
			 Class.forName (className);
			 } catch (ClassNotFoundException e) {
			 e.printStackTrace();
			 }
			 
			 Connection con = DriverManager.getConnection(url, user, password);
			 PreparedStatement st = con.prepareStatement(selectStatementDate);
				st.setString(1, codigoFrom);
				st.setString(2, codigoTo);
				ResultSet rs = st.executeQuery();
			 FillTable model = new FillTable(rs);
			 return model;
			 }
}
